from datetime import datetime
from time import sleep

import pandas as pd
import requests

from detail_request_params import GlBookDetailRequestParams
from get_config import get_token

##获取科目信息，
##获取sid
##获取数据以及列表，写入excel
''''
json如下：
[{'accountDate': '2025-03-31', 'docTypeAndCode': '记-00006', 'docId': '588706150632002', 'summary': '结息', 'currencyAndExchangeRate': '人民币/1.000000', 'quantityDr': '', 'priceDr': '', 'origAmountDr': '-22.89', 'amountDr': '-22.89', 'quantityCr': '', 'priceCr': '', 'origAmountCr': '', 'amountCr': '', 'balanceDirection': '贷', 'balanceQuantity': '', 'balancePrice': '', 'balanceOrigAmount': '22.89', 'balanceAmount': '22.89'}，{'accountDate': '2025-01', 'docTypeAndCode': '', 'docId': '', 'summary': '期初余额', 'currencyAndExchangeRate': '', 'quantityDr': '', 'priceDr': '', 'origAmountDr': '', 'amountDr': '', 'quantityCr': '', 'priceCr': '', 'origAmountCr': '', 'amountCr': '', 'balanceDirection': '平', 'balanceQuantity': '', 'balancePrice': '', 'balanceOrigAmount': '', 'balanceAmount': ''}]
我想写入表格 panda，的name sheet表格（新建的excel），写入内容如图所示，对应的字段是accountDate，docTypeAndCode（凭证号），summary对应摘要，balanceDirection（方向）balanceOrigAmount余额 origAmountDr借方 origAmountCr贷方
'''

# 定义字段映射（源字段 -> 目标列名）
column_mapping = {
    'accountDate': '日期',
    'docTypeAndCode': '凭证字号',
    'summary': '摘要',
    'origAmountDr': '借方',
    'origAmountCr': '贷方',
    'balanceDirection': '方向',
    'balanceAmount': '余额'
}

def get_sid(beginDate, endDate=None,account_code=None):
    # 创建请求参数
    params = GlBookDetailRequestParams(beginDate=beginDate)
    ##设置科目
    if account_code:
        params.set_account_code(account_code)

    ##设置结束时间
    if endDate:
        params.set_enddate(endDate)
    else:
        params.set_enddate(datetime.now().strftime("%Y-%m"))


    # 生成请求体
    request_body = params.to_request_body()

    # 打印结果（调试用）
    print(request_body)

    TOKEN = get_token()

    url_start = "https://jcdz.jcszfw.com/v1/cw/asyncRequest/start"
    headers = {"Token": TOKEN, "Content-Type": "application/json"}

    response = requests.post(url_start, json=request_body, headers=headers)

    if response.status_code != 200:
        print(f"❌ 初始请求失败: {response.status_code} - {response.text}")
        exit(1)

    try:
        start_data = response.json()
        if not start_data.get("result"):
            print(f"❌ 业务失败: {start_data}")
            exit(1)
        sequence_no = start_data["value"]
        print(f"✅ 获取到 sequenceNo: {sequence_no}")

        return sequence_no

    except Exception as e:
        print(f"❌ 解析初始响应失败: {e}")
        exit(1)

def get_bill_detail(sequence_no):
    TOKEN = get_token()
    headers = {"Token": TOKEN, "Content-Type": "application/json"}
    # === 第二步：轮询查询结果 ===
    url_query = "https://jcdz.jcszfw.com/v1/cw/asyncRequest/queryResult"
    query_body = {"sequenceNo": sequence_no}

    resp = requests.post(url_query, json=query_body, headers=headers)

    try:
        # 取出数据
        data = resp.json()['value']['data']
        return data


    except Exception as e:
        sleep(5)
        resp = requests.post(url_query, json=query_body, headers=headers)
        data = resp.json()['value']['data']

        return data



def main():
    end_date = datetime.now().strftime("%Y-%m")

    ##1.获取科目信息 保存为字典
    sequence_no = get_sid(beginDate="2021-01", endDate=end_date)
    data = get_bill_detail(sequence_no)
    accountList = data['accountList']
    code_list = {
        item["code"]: item["codeAndName"]
        for item in accountList
        if item.get("isEndNode") is True
    }

    print(code_list)

    # 创建 ExcelWriter（注意：必须在循环外创建，否则会覆盖）
    with pd.ExcelWriter('账务明细汇总.xlsx', engine='openpyxl') as writer:
        for code, name in code_list.items():
            sequence_no = get_sid(beginDate="2021-01", endDate=end_date, account_code=code)
            data_bill = get_bill_detail(sequence_no)
            data_list=data_bill['dataList']['data']

            print(code, name)
            print(data_list)

            # 2. 转为 DataFrame
            if not data_list:
                # 如果无数据，创建空表头
                df = pd.DataFrame(columns=column_mapping.values())
            else:
                df = pd.DataFrame(data_list)

                # 保留需要的列（防止某些字段缺失报错）
                available_cols = [col for col in column_mapping.keys() if col in df.columns]
                df = df[available_cols].rename(columns=column_mapping)

                # # 可选：数值列转为数字（空字符串变为空）
                # for col in ['借方', '贷方', '余额']:
                #     if col in df.columns:
                #         df[col] = pd.to_numeric(df[col], errors='coerce')

            # 3. 写入 sheet，sheet 名 = code（或 name，根据你需求）
            # 注意：Excel sheet 名不能超过 31 个字符，且不能含 \ / ? * [ ] 等特殊字符
            safe_sheet_name = str(name)[:31]  # 截断并转字符串，确保合法

            df.to_excel(writer, sheet_name=safe_sheet_name, index=False)






    ## 循环遍历 获取数据

    ##写入数据


if __name__ == "__main__":
    main()